package com.luobo.pcsdepositmanage;

import com.luobo.pcsdepositmanage.util.ExcelOperationUtil;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class operaExcel {
    private static Logger LOGGER = Logger.getLogger(operaExcel.class);

    private Workbook workbook;
    private String filedir = "/Users/liuminghao/Documents/tmp";
    private String jkFilename = "接口文件2.xlsx";
    private String jkSheetname = "Sheet1";

    private String templatefilename = "统一柜面管理平台交易清单-更新至新核心交易-3.15更新.xlsx";
    private String templateSheetname = "Sheet1";

    private String resultFileName = "统一柜面管理平台交易清单-结果.xlsx";
    private List<Map<Integer,Object>> pcsList;
    private List<Map<Integer,Object>> templateList;

    @Test
    public void operaTmp(){
        try {
            this.workbook = ExcelOperationUtil.getWorkbookFromExcel(this.filedir+File.separator+this.templatefilename);
            Map<String,Object> paraMap  = new HashMap<>();

            int headrownum = 1;
            paraMap.put("filedir",this.filedir);
            paraMap.put("filename",jkFilename);
            paraMap.put("sheetname",jkSheetname);
            paraMap.put("headrownum",headrownum);

            this.pcsList = ExcelOperationUtil.readExcelFromFileForMapToListMap(paraMap);

            headrownum = 3;
            paraMap.put("filename",templatefilename);
            paraMap.put("sheetname",templateSheetname);
            paraMap.put("headrownum",headrownum);

            Sheet templateSheet = ExcelOperationUtil.getSheet(this.workbook,paraMap);
            int physicalNumberOfRows = 1;

//            this.templateList = ExcelOperationUtil.readExcelFromFileForMapToListMap(paraMap);
            Row operaRow = null;
            int alladdnum = 0;
            int mvNum = 1;
            for (int i = 3; i < templateSheet.getPhysicalNumberOfRows() + alladdnum-1; i+=mvNum) {
                operaRow = templateSheet.getRow(i);
                if(operaRow == null){
                    break;
                }
                System.out.println("物理行数："+templateSheet.getPhysicalNumberOfRows());
                if(operaRow.getCell(10) == null){
                    System.out.println("sss");
                    continue;
                }
                System.out.println("读取文件"+i);
                mvNum = 1;
                String tempString = ExcelOperationUtil.getStringValue(operaRow.getCell(10));
                if(tempString.equals("（外币）本外币活期一本通开户")){
                    System.out.println(tempString);
                }
//                if(i == 684){
//                    System.out.println("saaa");
//                }
                if (operaRow != null && operaRow.getCell(10) != null && isRun(tempString)){


                    int startNum = i + 1;
                    int endNum = templateSheet.getLastRowNum()+1;
                    List<Map<String,String>> resultList = getResultList(tempString);
                    mvNum = resultList.size()-1;
                    ExcelOperationUtil.setCellForIndex(operaRow,20,resultList.get(0).get("pcscode"));
                    ExcelOperationUtil.setCellForIndex(operaRow,21,resultList.get(0).get("pcsname"));

                    if(mvNum >= 1){
                        templateSheet.shiftRows( startNum, endNum, mvNum, true, false);
                        alladdnum += mvNum;
                        for (int k = 1; k < resultList.size(); k++) {
                            Row tempRow = templateSheet.getRow(i + k);
                            if(tempRow == null){
                                tempRow = templateSheet.createRow(i + k);
                            }
                            ExcelOperationUtil.copyRow(operaRow,tempRow);
//                            ExcelOperationUtil.setCellForIndex(tempRow,8,ExcelOperationUtil.getValue(operaRow.getCell(8)));
//                            ExcelOperationUtil.setCellForIndex(tempRow,9,ExcelOperationUtil.getValue(operaRow.getCell(9)));
//                            ExcelOperationUtil.setCellForIndex(tempRow,10,ExcelOperationUtil.getValue(operaRow.getCell(10)));
                            ExcelOperationUtil.setCellForIndex(tempRow,20,resultList.get(k).get("pcscode"));
                            ExcelOperationUtil.setCellForIndex(tempRow,21,resultList.get(k).get("pcsname"));
//                            ExcelOperationUtil.setCellForIndex(tempRow,20,resultList.get(k).get("pcsname")+"（"+resultList.get(k).get("pcscode")+"）");
//                            for (int l = 13; l < 30; l++) {
//                                ExcelOperationUtil.setCellForIndex(tempRow,l,ExcelOperationUtil.getValue(operaRow.getCell(l)));
//                            }
                        }
                    }
                    mvNum += 1;


                }
            }

            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行

//            List<CellRangeAddress> cellRangeAddressList = templateSheet.getMergedRegions();
//            for (int i = 0; i < cellRangeAddressList.size(); i++) {
//                CellRangeAddress region  = cellRangeAddressList.get(i);
//                if((region.getFirstRow() >= 142 || region.getLastRow() <= 147 || region.getFirstRow() >= 181 || region.getLastRow() <= 224) && (region.getFirstColumn() == 6 || region.getFirstColumn() == 7)) {
//                    templateSheet.removeMergedRegion(i);
//                }
//            }
//
//
//            CellRangeAddress region = new CellRangeAddress(142, 147, 6, 6);
//            templateSheet.addMergedRegion(region);
//            CellRangeAddress region2 = new CellRangeAddress(142, 147, 7, 7);
//            templateSheet.addMergedRegion(region2);
//            CellRangeAddress region3 = new CellRangeAddress(181, 224, 6, 6);
//            templateSheet.addMergedRegion(region3);
//            CellRangeAddress region4 = new CellRangeAddress(181, 224, 7, 7);
//            templateSheet.addMergedRegion(region4);

            System.out.println("写入文件开始");
            writeExcel();
            System.out.println("写入文件完成");

        } catch (Exception e) {
            e.printStackTrace();
        }


    }


    public boolean isRun(String paraStr){
        boolean resultBoolean = false;
        for (int i = 0; i < this.pcsList.size(); i++) {
            if(this.pcsList.get(i).get(0).equals(paraStr)){
                resultBoolean = true;
            }
        }
        return  resultBoolean;

    }

    /*
执行写入excel文件任务
 */
    public void writeExcel(){
        try {
            ExcelOperationUtil.writeWorkbookToFile(this.workbook,this.filedir+ File.separator+this.resultFileName);
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.info(e.getStackTrace().toString());

        }
    }


    public List<Map<String,String>> getResultList(String paraStr) {
        List<Map<String,String>>  resultMapList = new ArrayList<>();

        for (int i = 0; i < this.pcsList.size(); i++) {
            if (this.pcsList.get(i).get(0).equals(paraStr)){
                Map<String,String> tempMap = new HashMap<>();
                tempMap.put("pcscode",(String) this.pcsList.get(i).get(1));
                tempMap.put("pcsname",(String) this.pcsList.get(i).get(2));
                resultMapList.add(tempMap);

            }
        }

        return resultMapList;
    }

}
